﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using Model;
using System.Reflection;
using System.Text;
using System.Web.Services;




/// <summary>
///Access 的摘要说明
/// </summary>
public class Access
{
    #region userinfo
    public DataSet Veiry(string uid, string pwd)
    {
        string sql = "SELECT u.*,m.Name FROM dbo.UserInfo u LEFT JOIN dbo.Merchants m ON u.mid=m.ID where uid=@uid and pwd=@pwd";
        SqlParameter[] paras = new SqlParameter[]{
        new SqlParameter("@uid",uid),
        new SqlParameter("@pwd",pwd)
        };
        DataSet ds = SqlHelp.GetDataSet(sql, paras);
        return ds;
    }

    public void UpdateUserInfo(UserInfo user)
    {
        string sql = "update userinfo set username=@username,uid=@uid,pwd=@pwd,sex=@sex,createtime=@createtime,updatetime=@updatetime,skin=@skin,mid=@mid where id=@id";
        SqlParameter[] para = new SqlParameter[] { 
        new SqlParameter("@id",user.ID),
        new SqlParameter("@UserName",user.UserName),
        new SqlParameter("@uid",user.UID),
        new SqlParameter("@pwd",user.PWD),
        new SqlParameter("@sex",user.Sex),
        new SqlParameter("@CreateTime",user.CreateTime),
        new SqlParameter("@Updatetime",user.UpdateTime),
        new SqlParameter("@skin",user.Skin),
        new SqlParameter("@mid",user.MID)
        };
        SqlHelp.Excute(sql, para);
    }


    public void DeleteWeiMenu(string id)
    {
        string sql = "delete from  weimenu where id=@id ";
        SqlParameter[] paras = new SqlParameter[] { 
        new SqlParameter("@id",id)
        };
        SqlHelp.Excute(sql, paras);
    }

    public DataSet GetAllUser(string sort,int pageSize,int pageNumber,string condition)
    {
        string sql = "select count(*) from userInfo "+
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT u.*,m.Name FROM dbo.UserInfo u LEFT JOIN dbo.Merchants m ON u.mid=m.ID where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllCard(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from membershipcard " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select ID,Type,Title,CreateDate,CID ,SID ,'' as CityName,'' as Name ,CASE type WHEN 1 THEN '特权' WHEN 2 THEN '券' ELSE '会员卡说明' END type2 from membershipcard where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllCardUser(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from carduser " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select c.* from carduser c LEFT JOIN dbo.Merchants m ON c.ToUserName=m.ToUserName where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllPerson(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from PersonInfo as a left join STORE as b on  a.Store=b.CODE where " + condition + " " +
            "select *  from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select a.*,b.name as bname from PersonInfo as a left join STORE as b on  a.Store=b.CODE where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllIntegralDetail(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) FROM dbo.IntegralChange i LEFT JOIN dbo.PersonInfo p ON i.PID=p.ID LEFT JOIN dbo.PersonInfo p1 ON p.Superior=p1.PersonNo  where " + condition + " " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT p.Name,p.jt,p.Store,P.PersonNo,i.* FROM  dbo.IntegralChange i LEFT JOIN dbo.PersonInfo p ON i.PID=p.ID LEFT JOIN dbo.PersonInfo p1 ON p.Superior=p1.PersonNo where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllIntegralDetailExcel( string condition)
    {
        string sql = "SELECT p.Name '姓名',p.jt '所属集团',p.Store '所属门店',Time '时间',Action '操作',Integral '积分变化' ,i.SurplusIntegral '剩余积分' FROM  dbo.IntegralChange i LEFT JOIN dbo.PersonInfo p ON i.PID=p.ID LEFT JOIN dbo.PersonInfo p1 ON p.Superior=p1.PersonNo  where " + condition;
        return SqlHelp.GetDataSet(sql, null);
    }

//    public DataSet GetPersonExcel(string condition)
//    {
//        string sql = @"SELECT Name '姓名',PersonNo '员工编号',Phone '手机',jt '所属集团',Store '所属店铺',
//ZW '职务',HYSJ '会员手机',SHDZ '收货地址',SHSheng '收货省份',SHShi'收货市区',SHR'收货人',
//SHRLXDH'收货人联系电话',Post'收货人邮编',RHRQ'入会日期',LHRQ'离会日期',CASE HYZT WHEN 0 THEN '在职' ELSE '离职' END '会员状态',
//SumIntegral '累计积分',SurplusIntegral '剩余积分',JLJF'奖励积分',GQJF'过期积分' FROM dbo.PersonInfo where " + condition;
//        return SqlHelp.GetDataSet(sql, null);
//    }

    public DataSet GetPersonExcel(string condition)
    {
        string sql = @"select Name as  姓名, case  Sex  when  1 then '男' else '女' end sex ,Phone as 联系电话, birthday as 生日,EMAIL as 电子邮件,addr as 地址,PersonNo as 会员卡号,FromUserName as 微信id , WXName 微信名称  from  (select *,ROW_NUMBER() over( order by ID asc) as number from (select a.*,b.name as bname from PersonInfo as a left join STORE as b on  a.Store=b.CODE where  1=1  ) as b ) as c  where " + condition + " order by ID asc";
        return SqlHelp.GetDataSet(sql, null);
    }


    public DataSet GetPersonAddressExcel(string condition)
    {
        string sql = @"SELECT Phone '手机',jt '所属集团',Store '所属店铺',
ZW '职务',CASE ZW WHEN '店长' THEN PersonNo ELSE Superior END '店长编号',HYSJ '会员手机',SHSheng '省份',SHShi'城市',SHDZ '地址',SHR'收货人',
SHRLXDH'收货人联系电话',Post'邮编',CASE HYZT WHEN 0 THEN '在职' ELSE '离职' END '会员状态' FROM dbo.PersonInfo where " + condition;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllOrder(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "SELECT count(*) FROM dbo.REDP_HEAD r LEFT JOIN dbo.PersonInfo p ON r.MEMBERNO=p.PersonNo LEFT JOIN dbo.Address a ON r.AddressID=a.ID where " + condition + " " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT r.ID,r.TRANSDATE,r.MEMBERNO,r.POINTS,r.STATUS,p.Name,a.Address ADDRESS,a.Post,a.Phone FROM dbo.REDP_HEAD r LEFT JOIN dbo.PersonInfo p ON r.MEMBERNO=p.PersonNo LEFT JOIN dbo.Address a ON r.AddressID=a.ID where " + condition + ") as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetOrderMX(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "SELECT count(*) FROM REDP_DETAIL where " + condition + " " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT * FROM dbo.REDP_DETAIL WHERE  " + condition + ") as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllOrderExcel(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "SELECT p.JT'所属集团',p.Store'所属门店',p.Name '姓名',p.Phone '手机',ISNULL(a.Name,p.SHR)  '收货人',ISNULL(a.Phone,p.SHRLXDH)  '收货人联系电话',ISNULL(a.Sheng,p.SHSheng)+'省'+ISNULL(a.Shi,p.SHShi)+'市'+ISNULL(a.Address,p.SHDZ)  '收货地址',g.Name '礼品名称',t.CreateDate '下单时间',t.Number '兑换数量',t.Number*g.Integral '所用积分',CASE t.States WHEN 1 THEN '已下单' when 3 then '已备货' when 4 then '已收货' ELSE '已发货' END  '订单状态',t.ID '兑换号' FROM dbo.TheOrder t LEFT JOIN dbo.PersonInfo p ON t.PID=p.ID LEFT JOIN dbo.Address a ON t.AID=a.ID LEFT JOIN dbo.Gift g ON t.GID=g.ID LEFT JOIN dbo.PersonInfo p1 ON p.Superior=p1.PersonNo where " + condition;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllGift(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from PRODUCT where " + condition + "  " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select ID,SKU,Name,Integral,Price,begin_inv,REDP_INV,SALES_INV,States,CreateDate,CASE IF_STAR WHEN 0 THEN '否' ELSE '是' END mx,CASE IF_SALES WHEN 0 THEN '否' ELSE '是' END xl,CASE IF_TOP WHEN 0 THEN '否' ELSE '是' END tj,CASE IF_NEW WHEN 0 THEN '否' ELSE '是' END xp,CASE ISNULL(IF_DISP,0) WHEN 0 THEN '否' ELSE '是' END ZS,CASE ISNULL(IF_REDP,0) WHEN 0 THEN '否' ELSE '是' END DL from PRODUCT where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllTicket(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from Ticket where " + condition + "  " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT ID,Name,BegDate,EndDate,TotalNum,UsableNum,Crowd,CASE IsShow when 1 THEN '是' ELSE '否' end show,type from Ticket where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllStore(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "SELECT count(*) FROM STORE AS A left join SAP_DICT AS B on A.CITY=B.CODE AND B.BRAND='VIVE'AND B.TYPE='CITY'  where " + condition + " " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT A.*,B.NAME cityname FROM STORE AS A left join SAP_DICT AS B on A.CITY=B.CODE AND B.BRAND='VIVE'AND B.TYPE='CITY' where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllGiftExcel(string condition)
    {
        string sql = "select SKU '编号',Name '名称',Integral '兑换积分',Price '价格',begin_inv '仓库余量(非实时)',REDP_INV  '兑换限量',SALES_INV '销售限量' ,CASE IF_STAR WHEN 0 THEN '否' ELSE '是' END '是否明星',CASE IF_SALES WHEN 0 THEN '否' ELSE '是' END '是否销量',CASE IF_TOP WHEN 0 THEN '否' ELSE '是' END '是否推荐',CASE IF_NEW WHEN 0 THEN '否' ELSE '是' END '是否新品',CASE ISNULL(IF_DISP,0) WHEN 0 THEN '否' ELSE '是' END '是否展示',CASE ISNULL(IF_REDP,0) WHEN 0 THEN '否' ELSE '是' END '是否兑礼' from PRODUCT where " + condition;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllMessage(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from promotion " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT ID,RTRIM(LTRIM(Name)) Name,BeginDate,Enddate,CASE PTYPE WHEN 1 THEN '特惠' WHEN 2 THEN '专享' ELSE '活动' END ptype,CASE STATUS WHEN 0 THEN '进行中' WHEN 1 THEN '即将进行' ELSE '已结束' END status  FROM dbo.promotion where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    //public DataSet GetAllRequest(string sort, int pageSize, int pageNumber, string condition)
    //{
    //    string sql = "select count(*) from dbo.WeiXinRequest where " + condition +
    //        "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT ID,FromUserName,REPLACE(REPLACE(ltrim(rtrim(Content)),' ',''),'#','') CONTENT,CreateTime,REPLACE(REPLACE(ltrim(rtrim(PicUrl)),' ',''),'#','') PicUrl FROM dbo.WeiXinRequest   where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
    //    return SqlHelp.GetDataSet(sql, null);
    //}

    public DataSet GetAllRequest(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from dbo.WeiXinRequest where " + condition +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT w.ID,w.FromUserName,REPLACE(REPLACE(ltrim(rtrim(Content)),' ',''),'#','') CONTENT,CreateTime,REPLACE(REPLACE(ltrim(rtrim(PicUrl)),' ',''),'#','') PicUrl,p.PersonNo,'' sj,'' nc,p.Name,CASE p.Sex WHEN 1 THEN '男' ELSE '女' END sex  FROM dbo.WeiXinRequest w LEFT JOIN dbo.PersonInfo p ON w.FromUserName=p.FromUserName   where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllHelpMessage(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from Helpmessage " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT id ID,Time,Title,JJ FROM Helpmessage where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllSales(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from SalesRecord " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select ID,PersonNo,Amount,Product,Number num,Time,Integral,CreateDate from SalesRecord ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllNewSales(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from NewSalesRecord " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT * FROM dbo.NewSalesRecord ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllNewSalesExcel()
    {
        string sql = "SELECT * FROM dbo.NewSalesRecord";
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllBuy(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from GroupBuying " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select ID,Title,OldPrice,NewPrice,Summary,CASE Status WHEN 1 THEN '启用' ELSE '停用' END Status from GroupBuying where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllMerchants(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from Merchants " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select ID,Name,ToUserName from Merchants where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllBuyUser(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from GroupBuyingUser " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select u.ID,g.Title,Name,Phone,FromUserName,CreateDate,CASE u.States WHEN 1 THEN '未使用' ELSE '已使用' END Status,num from GroupBuyingUser u LEFT JOIN dbo.GroupBuying g ON u.GID=g.ID where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllIntegralChange(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from IntegralChange where " + condition + " " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select * from IntegralChange where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllIntegralChangeExcel( string condition)
    {
        string sql = "SELECT Time '时间',Action '操作',Integral '积分变化',SurplusIntegral '剩余积分' FROM dbo.IntegralChange where " + condition;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllInventory(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from Inventory where " + condition + " " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select * from Inventory where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetInventoryExcel(string condition)
    {
        string sql = "SELECT g.Name '商品名称',GiftNo '商品编号', Time '时间',Num '增加库存' FROM dbo.Inventory i LEFT JOIN dbo.Gift g ON i.GID=g.ID where" + condition;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetCardToUse(string sort, int pageSize, int pageNumber, string condition,string con)
    {
        string sql = "SELECT count(*) FROM dbo.Merchants s INNER JOIN  dbo.CardUser c ON s.ToUserName=c.ToUserName AND " + con + "  LEFT JOIN dbo.CardToUse u ON c.ID=u.CID AND " + condition + " LEFT JOIN dbo.MembershipCard m ON u.MID=m.ID  " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (SELECT c.*,CASE WHEN  u.ID IS  NULL THEN '未使用' ELSE '已使用' END Status FROM dbo.Merchants s INNER JOIN  dbo.CardUser c ON s.ToUserName=c.ToUserName AND "+con+"  LEFT JOIN dbo.CardToUse u ON c.ID=u.CID AND "+condition+" LEFT JOIN dbo.MembershipCard m ON u.MID=m.ID   ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetAllInfo(string sort, int pageSize, int pageNumber, string condition)
    {
        string sql = "select count(*) from information " +
            "select * from  (select *,ROW_NUMBER() over(" + sort + ") as number from (select ID,Title,KeyWords,CASE replyType WHEN 1 THEN '关键字回复' WHEN 2 THEN '被关注回复' when 4 then '自定义菜单' ELSE '自动回复' END  hh,CASE MsgType WHEN 'news' THEN '图文' WHEN 'music' THEN '音乐' ELSE '文本' END  xx from information where " + condition + " ) as b ) as c " + " where  number  between " + (pageNumber * pageSize + 1) + " and " + ((pageNumber + 1) * pageSize) + "" + sort;
        return SqlHelp.GetDataSet(sql, null);
    }

    public void AddUser(UserInfo user)
    {
        string sql = "insert into userinfo values(@username,@uid,@pwd,@sex,@createTime,@updateTime,@skin,@mid)";
        SqlParameter[] para = new SqlParameter[]{
        new SqlParameter("@username",user.UserName),
        new SqlParameter("@uid",user.UID),
        new SqlParameter("@pwd",user.PWD),
        new SqlParameter("@sex",user.Sex),
        new SqlParameter("@createTime",user.CreateTime),
        new SqlParameter("@updateTime",user.UpdateTime),
        new SqlParameter("@skin",user.Skin),
        new SqlParameter("@mid",user.MID)
        };
        SqlHelp.Excute(sql, para);
    }

    public UserInfo GetUserByUID(string UID)
    {
        UserInfo user = null;
        string sql = "select * from userinfo where uid=@uid";
        SqlParameter[] para = new SqlParameter[]{
        new SqlParameter("@uid",UID)
        };
        DataRowCollection drC = SqlHelp.GetDataSet(sql, para).Tables[0].Rows;
        if (drC.Count > 0)
        {
            DataRow dr = drC[0];
            user = new UserInfo();
            user.ID = Convert.ToInt32(dr["id"]);
            user.UserName = dr["userName"].ToString();
            user.UID = dr["uid"].ToString();
            user.PWD = dr["pwd"].ToString();
            user.Sex = dr["sex"].ToString();
            user.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
            user.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]);
            user.Skin = dr["skin"].ToString();
            user.MID = int.Parse(dr["mid"].ToString());
        }
        return user;
    }

    public UserInfo GetUserByID(string ID)
    {
        UserInfo user = null;
        string sql = "select * from userinfo where id=@id";
        SqlParameter[] para = new SqlParameter[]{
        new SqlParameter("@id",ID)
        };
        DataRowCollection drC = SqlHelp.GetDataSet(sql, para).Tables[0].Rows;
        if (drC.Count > 0)
        {
            DataRow dr = drC[0];
            user = new UserInfo();
            user.ID = Convert.ToInt32(dr["id"]);
            user.UserName = dr["userName"].ToString();
            user.UID = dr["uid"].ToString();
            user.PWD = dr["pwd"].ToString();
            user.Sex = dr["sex"].ToString();
            user.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
            user.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]);
            user.Skin = dr["skin"].ToString();
            user.MID = int.Parse(dr["mid"].ToString());
        }
        return user;
    }

    public void DelteUserByID(string ID)
    {
        string sql = "delete from userinfo where id=@id";
        SqlParameter[] para=new SqlParameter[]{
        new SqlParameter("@id",ID)
        };
        SqlHelp.Excute(sql, para);
    }

    public DataSet SelectUserByUID(string uid)
    {
        string sql = "select * from userinfo where uid=@uid";
        SqlParameter[] paras = new SqlParameter[] { 
        new SqlParameter("@uid",uid)
        };
        return SqlHelp.GetDataSet(sql, paras);
    }
    #endregion

    #region menu
    public DataSet GetMenuEntityById(string id)
    {
        string sql = "select * from menu where id=" + id;
        return SqlHelp.GetDataSet(sql, null);
    }

    public void UpdateMenu(Model.Menu menu)
    {
        string sql = "update menu set MenuName=@MenuName,MenuIcon=@MenuIcon,MenuUrl=@MenuUrl,Suqence=@Suqence,CreateTime=@CreateTime,UpdateTime=@UpdateTime,ParentID=@ParentID where id=@id";
        SqlParameter[] paras = new SqlParameter[] { 
        new SqlParameter("@id",menu.ID),
        new SqlParameter("@MenuName",menu.MenuName),
        new SqlParameter("@MenuIcon",menu.MenuIcon),
        new SqlParameter("@MenuUrl",menu.MenuUrl),
        new SqlParameter("@Suqence",menu.Suqence),
        new SqlParameter("@CreateTime",menu.CreateTime),
        new SqlParameter("@UpdateTime",menu.UpdateTime),
        new SqlParameter("@ParentID",menu.ParentID)
        };
        SqlHelp.Excute(sql, paras);
    }
    public void AddMenu(Model.Menu menu)
    {
        string sql = "insert into menu values( @MenuName,@MenuIcon,@MenuUrl,@Suqence,@CreateTime,@UpdateTime,@ParentID)";
        SqlParameter[] paras = new SqlParameter[] { 
        new SqlParameter("@MenuName",menu.MenuName),
        new SqlParameter("@MenuIcon",menu.MenuIcon),
        new SqlParameter("@MenuUrl",menu.MenuUrl),
        new SqlParameter("@Suqence",menu.Suqence),
        new SqlParameter("@CreateTime",menu.CreateTime),
        new SqlParameter("@UpdateTime",menu.UpdateTime),
        new SqlParameter("@ParentID",menu.ParentID)
        };
        SqlHelp.Excute(sql, paras);
    }
    public void DeleteMenu(string id)
    {
        string sql = "delete from  menu where id=@id ";
        SqlParameter[] paras = new SqlParameter[] { 
        new SqlParameter("@id",id)
        };
        SqlHelp.Excute(sql, paras);
    }
    #endregion

    public DataSet GetMenuData(string parentid,string user)
    {

        string sql = @"  select a.*,case
        when exists(select 1 from menu where ParentID=a.id) then 'haveC' 
        else 'noC'
        end as haveC
          from menu as a LEFT JOIN dbo.Role r ON a.ID=r.ParentID where  a.parentid="+parentid;
        if(user!=null)
            sql+=" AND r.Seqence=" + user;
        if (user == null)
            sql = @"select a.*,case
        when exists(select 1 from menu where ParentID=a.id) then 'haveC' 
        else 'noC'
        end as haveC
          from menu as a ";
        sql += "  ORDER BY a.Suqence";
        DataSet ds = SqlHelp.GetDataSet(sql, null);
        return ds;
    }

    public DataSet GetMenuData2(string uid)
    {

        string sql = @"select a.ID,a.MenuName,r.Id  rid,a.ParentID  from menu as a
LEFT JOIN dbo.Role r ON a.ID=r.ParentID AND r.Seqence=" +uid+" ORDER BY a.ParentID ";
        DataSet ds = SqlHelp.GetDataSet(sql, null);
        return ds;
    }

    public DataTable GetNewMenu(string uid)
    {
        string sql = string.Format(@"select a.*,case when exists(select 1 from menu where ParentID=a.id) then 'haveC' else 'noC' end as haveC
                        from menu as a LEFT JOIN dbo.Role r ON a.ID=r.ParentID where  a.parentid=1 AND r.Seqence={0} 
                        UNION ALL
                        SELECT a.*,CASE when exists(select 1 from menu where ParentID=a.id) then 'haveC' else 'noC' end as haveC 
                        FROM  dbo.Menu a LEFT JOIN dbo.Role r ON a.ID=r.ParentID WHERE r.Seqence={0} AND   a.ParentID IN (SELECT id FROM dbo.Menu WHERE ParentID=1)
                        UNION ALL
                        SELECT a.*,case when exists(select 1 from menu where ParentID=a.id) then 'haveC' else 'noC' end as haveC 
                        FROM  dbo.Menu a LEFT JOIN dbo.Role r ON a.ID=r.ParentID WHERE r.Seqence={0}  AND a.ParentID IN (SELECT id FROM dbo.Menu WHERE ParentID IN(SELECT id FROM dbo.Menu WHERE ParentID=1))
                        ORDER BY a.ParentID,a.Suqence",uid);
        return SqlHelp.GetDataSet(sql, null).Tables[0];
    }

    public DataTable GetWeiMenuByMid(int mid)
    {
        string sql = @"SELECT * FROM (
        select *,case when exists(select 1 from weimenu where ParentID=a.id) then 'haveC' else 'noC' end as haveC
                        from weimenu as a where  parentid=0 
                        UNION ALL
                        SELECT *,CASE when exists(select 1 from weimenu where ParentID=a.id) then 'haveC' else 'noC' end as haveC 
                        FROM  dbo.weimenu a WHERE ParentID IN (SELECT id FROM dbo.weimenu WHERE ParentID=0)
                        )t WHERE t.MID=" + mid + " ORDER BY t.ParentID,t.Suqence";
        return SqlHelp.GetDataSet(sql, null).Tables[0];
    }

    public DataSet GetWeiMenuEntityById(string id)
    {
        string sql = "select * from weimenu where id=" + id;
        return SqlHelp.GetDataSet(sql, null);
    }

    public DataSet GetWeiMenuData(string parentid, int mid)
    {

        string sql = @"select *,'icon-tip' MenuIcon,case
        when exists(select 1 from weimenu where ParentID=a.id) then 'haveC' 
        else 'noC'
        end as haveC
          from weimenu as a where  parentid=" + parentid + " and mid=" + mid;
        DataSet ds = SqlHelp.GetDataSet(sql, null);
        return ds;
    }


    public DataSet GetWenjuanExcel()
    {
        string sql = "select * from v_Ans";
        return SqlHelp.GetDataSet(sql, null);
    }
}